<?php
/**
* Yapp select wrapper
*/

class Select extends operationsBase
{

    // Own for select
    private $joinStart      = false;
    private $joins          = array();
    private $fieldsFromJoin = array();
    private $lastTableJoin  = false;
    protected $fieldList      = false;
    private $groupByArr     = array();
    private $orderByArr     = array();
    private $offsetAndLimit = false;
    private $havingFilters  = false;

    public function from($tableName, $database = false)
    {
        return $this->_from($tableName, $database);
    }

    public function fields($args)
    {
        if ($this->killChaining) return $this;
        if (empty($args))
        {
            $this->killChaining = true;
            $this->lastError    = "Field funciont must have at least one argument (* is allowed!)";
        }
        else
        {
            $fieldList = array();
            foreach ($args as $field)
            {
                // Check if field is operation such as COUNT SUM MAX DISTINCT
                $field = strtolower(trim($field));
                if (($countOperationPos = strpos($field, 'count(')) !== false)
                {
                    $sanitized = $this->getTableAndFieldFromOperation('count', $field);
                }
                else if (($sumOperationPos = strpos($field, 'sum(')) !== false)
                {
                    $sanitized = $this->getTableAndFieldFromOperation('sum', $field);
                }
                else if (($maxOperationPos = strpos($field, 'max(')) !== false)
                {
                    $sanitized = $this->getTableAndFieldFromOperation('max', $field);
                }
                else if (($distinctOperationPos = strpos($field, 'distinct(')) !== false)
                {
                    $sanitized = $this->getTableAndFieldFromOperation('distinct', $field);
                }
                else
                {
                    $field     = str_replace('(', '', $field);
                    $field     = str_replace(')', '', $field);
                    $sanitized = $this->getTableAndField($field);
                }
                if($sanitized)
                {
                    $fieldList[]=$sanitized;
                }
            }
            $this->fieldList = $fieldList;
        }
        return $this;
    }

    public function join($tableName)
    {
        return $this->doJoin($tableName);
    }

    public function leftJoin($tableName)
    {
        return $this->doJoin($tableName, 'LEFT');
    }

    public function rightJoin($tableName)
    {
        return $this->doJoin($tableName, 'RIGHT');
    }

    public function joinAs($tableName, $alias)
    {
        return $this->doJoin($tableName, 'INNER', $alias);
    }

    public function leftJoinAs($tableName, $alias)
    {
        return $this->doJoin($tableName, 'LEFT', $alias);
    }

    public function rightJoinAs($tableName, $alias)
    {
        return $this->doJoin($tableName, 'RIGHT', $alias);
    }

    public function on($left, $middle = false, $root = false)
    {
        // Check kill
        if ($this->killChaining) return $this;
        if (!empty($this->lastTableJoin) && (empty($this->joins[$this->lastTableJoin]['on'])))
        {
            // Start building filter
            if (empty($middle) && empty($root) && (is_a($left, 'dbFilter')))
            {
                $filter = $this->parseFilter($left);
                $this->joins[$this->lastTableJoin]['on'] = $filter;
            }
            else
            {
                if (!empty($middle) && !empty($root))
                {
                    $filter = filter($left, $middle, $root);
                    $filter = $this->parseFilter($filter);
                    $this->joins[$this->lastTableJoin]['on'] = $filter;
                }
                else
                {
                    $this->killChaining = true;
                    $this->lastError    = "wrong join filters";
                }
            }
        }
        else
        {
            $this->killChaining = true;
            $this->lastError    = "Invalid join";
        }
        // Return self
        return $this;
    }

    // Notice that offset = 5 means that it will use from row 6 and above (5 is the last ignored result)
    public function limit($totalOrOffset, $total = false)
    {
        if ($total === false)
        {
            $limit  = intval($totalOrOffset);
            $offset = 0;
        }
        else
        {
            $offset  = intval($totalOrOffset);
            $limit = intval($total);
        }
        if ($limit > 0)
        {
            $this->offsetAndLimit = $this->dbObject->offsetAndLimit($offset, $limit);
        }
        else
        {
            $this->killChaining = true;
            $this->lastError    = "Total is not valid";
        }
        return $this;
    }

    public function getWithDebug(&$err = array())
    {
        if ($this->killChaining)
        {
            $err = $this->lastError;
            return false;
        }
        else
        {
            $sql = $this->buildSql();
            return $sql;
        }
    }

    public function get($one = false, $returnKey = false)
    {
        if ($this->killChaining)
        {
            return false;
        }
        else
        {
            $sql = $this->buildSql();
            if ($this->dbObject)
            {
                if (!empty($sql))
                {
                    $query = $this->dbObject->rawQuery($sql);
                    if ($query)
                    {
                        $numRows = $this->dbObject->numRows($query);
                        if ($numRows > 0)
                        {
                            $return = array();
                            while ($row = $this->dbObject->fetchAssoc($query))
                            {
                                if (isset($returnKey) && isset($row[$returnKey]))
                                {
                                    $return[$row[$returnKey]]=$row;
                                }
                                else
                                {
                                    $return[]=$row;
                                }
                            }
                            if ($one)
                            {
                                return $return[0];
                            }
                            else
                            {
                                return $return;
                            }
                        }
                        else
                        {
                            return false;
                        }
                    }
                    else
                    {
                        return false;
                    }
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return $sql;
            }
        }
    }

    public function sql()
    {
        return $this->buildSql();
    }

    private function doJoin($tableName, $type = 'INNER', $alias = false)
    {
        if ($this->killChaining) return $this;
        if (empty($tableName) || (empty($this->dbTypes[$this->database][$tableName])))
        {
            $this->killChaining = true;
            $this->lastError    = "Invalid table to join";
        }
        else
        {
            if (!empty($this->dbTypes[$this->database][$alias])) $alias = false;
            if ($this->lastTableJoin)
            {
                if (empty($this->joins[$this->lastTableJoin]['on']))
                {
                    $this->killChaining = true;
                    $this->lastError    = "Join for previous table ".$this->lastTableJoin. " is not valid. ON is missing.";
                }
                else
                {
                    if (!empty($this->joins[$tableName]))
                    {
                        if (empty($this->joins[$tableName]['as']) && (empty($alias)))
                        {
                            $this->killChaining = true;
                            $this->lastError    = "Join for table ".$this->lastTableJoin. " is not valid. Missing AS because table is the same as this.";
                        }
                        else if ($this->joins[$tableName]['as'] == $alias)
                        {
                            $this->killChaining = true;
                            $this->lastError    = "Join for table ".$this->lastTableJoin. " is not valid. Aliases are the same.";
                            if ($alias)
                            {
                                $this->auxTypes[$alias] = $tableName;
                            }
                        }
                        else
                        {
                            $this->joins[$tableName] = array('table' => $tableName, 'as' => $alias, 'on' => false, 'type' => $type);
                            $this->lastTableJoin = $tableName;
                            if ($alias)
                            {
                                $this->auxTypes[$alias] = $tableName;
                            }
                        }
                    }
                    else
                    {
                        $this->joins[$tableName] = array('table' => $tableName, 'as' => $alias, 'on' => false, 'type' => $type);
                        $this->lastTableJoin = $tableName;
                        if ($alias)
                        {
                            $this->auxTypes[$alias] = $tableName;
                        }
                    }
                }
            }
            else
            {
                $this->joins[$tableName] = array('table' => $tableName, 'as' => $alias, 'on' => false, 'type' => $type);
                $this->lastTableJoin = $tableName;
                if ($alias)
                {
                    $this->auxTypes[$alias] = $tableName;
                }
            }
        }
        return $this;
    }

    protected function getTableAndFieldFromOperation($operation, $field)
    {
        // Check amount of parenthesis
        $openCount = substr_count($field, '(');
        $closeCount = substr_count($field, ')');
        if (($openCount != 1) && ($closeCount != 1))
        {
            return false;
        }
        // Replacements
        $field = str_replace(' (', '(', $field);
        $field = str_replace(') as', ')as', $field);
        $field = str_replace($operation.'(', '', $field);
        // Explode by Alias (if it has)
        $alias = false;
        $splitted = explode(')as', $field);
        if (count($splitted) > 1)
        {
            foreach ($splitted as $k => $split)
            {
                $split = trim($split);
                if ($k == 0)
                {
                    $field = $split;
                }
                else
                {
                    $alias = $split;
                }
            }
        }
        else
        {
            $field = str_replace(')', '', $field);
        }
        if ($field == '*')
        {
            return false;
        }
        $sanitizedField = $this->getTableAndField($field);
        if ($alias) $sanitizedField['alias'] = $alias;
        $sanitizedField['operation'] = strtoupper($operation);
        return $sanitizedField;
    }

    protected function validateFields()
    {
        $fieldListOK = array();
        if (!empty($this->fieldList))
        {
            foreach($this->fieldList as $field)
            {
                if ($field['table'] == '')
                {
                    $table = $this->tableName;
                }
                else
                {
                    $table = $field['table'];
                }
                if (!empty($this->auxTypes[$table]))
                {
                    $auxTable = $table;
                    $table    = $this->auxTypes[$table];
                }
                if (!empty($this->dbTypes[$this->database][$table][$field['field']]))
                {
                    if (!empty($auxTable))
                    {
                        $fieldString = $auxTable.'.'.$field['field'];
                    }
                    else
                    {
                        $fieldString = $table.'.'.$field['field'];
                    }
                    if (!empty($field['operation']))
                    {
                        $fieldString = $field['operation'].'('.$fieldString.')';
                    }
                    if (!empty($field['alias']))
                    {
                        $fieldString = $fieldString.' AS '.$field['alias'];
                    }
                    $fieldListOK[]=$fieldString;
                }
                else if ((!empty($this->dbTypes[$this->database][$table])) && ($field['field'] == '*'))
                {
                    if (!empty($auxTable))
                    {
                        $fieldString = $auxTable.'.'.$field['field'];
                    }
                    else
                    {
                        $fieldString = $table.'.'.$field['field'];
                    }
                    if (!empty($field['operation']))
                    {
                        $fieldString = $field['operation'].'('.$fieldString.')';
                    }
                    if (!empty($field['alias']))
                    {
                        $fieldString = $fieldString.' AS '.$field['alias'];
                    }
                    $fieldListOK[]=$fieldString;
                }
                else
                {
                    $this->fieldsFromJoin[] = $field;
                }
            }
            $this->fieldList = $fieldListOK;
            if (empty($this->fieldList))
            {
                $this->killChaining = true;
                $this->lastError    = "Fields were not valid (* is allowed!)";
            }
        }
        else
        {
            $this->killChaining = true;
            $this->lastError    = "Fields were not valid (* is allowed!)";
        }
        return $this;
    }

    public function groupBy()
    {
        $args = func_get_args();
        if (!empty($args))
        {
            foreach($args as $field)
            {
                $field = $this->getTableAndField($field);
                if ($field['table'] == '')
                {
                    $table = $this->tableName;
                }
                else
                {
                    $table = $field['table'];
                }
                if (!empty($this->dbTypes[$this->database][$table][$field['field']]))
                {
                    $this->groupByArr[] = $table.'.'.$field['field'];
                }
            }
        }
        return $this;
    }

    public function having($left, $middle = false, $root = false)
    {
        // Check kill
        if ($this->killChaining) return $this;
        // Check if is already build filter
        if (empty($middle) && empty($root) && (is_a($left, 'dbFilter')))
        {
            $filter = $this->parseFilter($left);
            $this->havingFilters = $filter;
        }
        else
        {
            if (!empty($middle) && (!empty($root) || ($root === 0) || ($root === '0')))
            {
                // Start building filter
                $filter = filter($left, $middle, $root);
                $filter = $this->parseFilter($filter, true);
                $this->havingFilters = $filter;
            }
            else
            {
                $this->killChaining = true;
                $this->lastError    = "wrong filters for having";
            }
        }
        // Return self
        return $this;
    }

    public function orderBy()
    {
        $args = func_get_args();
        if (!empty($args))
        {
            foreach($args as $fieldOrder)
            {
                $split = explode(' ', $fieldOrder);
                $field = $split[0];
                $order = (!empty($split[1]))?(((strtoupper($split[1]) != 'DESC') && (strtoupper($split[1]) != 'ASC'))?'ASC':strtoupper($split[1])):'ASC';
                $field = $this->getTableAndField($field);
                if ($field['table'] == '')
                {
                    $table = $this->tableName;
                }
                else
                {
                    $table = $field['table'];
                }
                if (!empty($this->dbTypes[$this->database][$table][$field['field']]))
                {
                    $this->orderByArr[] = $table.'.'.$field['field'].' '.$order;
                }
            }
        }
        return $this;
    }

    private function buildSql()
    {
        // With all done, we validate fields
        $this->validateFields();
        if (empty($this->fieldList)) return false;
        // Build Select
        $sql  = 'SELECT ';
        $sql .= implode(', ',$this->fieldList);
        $sql .= ' FROM '.$this->tableName;
        if (!empty($this->joins))
        {
            foreach ($this->joins as $join)
            {
                if ((!empty($join['table'])) && (!empty($join['on'])))
                {
                    $sql .= ' '.$join['type'].' JOIN '.$join['table'].(!empty($join['as'])?(' AS '.$join['as'].' '):'').' ON '.$join['on'];
                }
            }
        }
        if (!empty($this->filter))
        {
            $sql .= ' WHERE '.$this->filter;
        }
        if (!empty($this->groupByArr))
        {
            $sql .= ' GROUP BY '.implode(',', $this->groupByArr);
        }
        if (!empty($this->havingFilters))
        {
            $sql .= ' HAVING '.$this->havingFilters;
        }
        if (!empty($this->orderByArr))
        {
            $sql .= ' ORDER BY '.implode(',', $this->orderByArr);
        }
        if (!empty($this->offsetAndLimit))
        {
            $sql .= ' '.$this->offsetAndLimit;
        }
        return $sql.';';
    }

}

?>